package com.yy;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;


import CustomClassFile.CronJob.ZswsHRaShift;
import net.sf.json.JSONObject;
import weaver.conn.RecordSet;
import weaver.general.StaticObj;

public class syncZsws {
	
	//同步排班

	public static void test4() {

		List<ZswsHRaShift> lists = new ArrayList<ZswsHRaShift>();
		JSONObject jsonObject = new JSONObject();
		String returnCode = "";
		String returnMessage = "";
		int add = 0;
		weaver.interfaces.datasource.DataSource ds = (weaver.interfaces.datasource.DataSource) StaticObj
				.getServiceByFullname(("datasource.HRDB"), weaver.interfaces.datasource.DataSource.class);
		java.sql.Connection conn = ds.getConnection();
		int datalength = 0;
		try {
			//查询今天之后的排班
			String sql = "select eid,name,shift,convert(varchar(19),term,20) as term,title,xtype from zswsHRaShift where convert(date,term,20) >convert(date,getdate(),20) order by eid ,term";
			PreparedStatement preparedStatement = conn.prepareStatement(sql);
			ResultSet resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {

				String eid = resultSet.getString("eid");
				String name = resultSet.getString("name");
				String shift = resultSet.getString("shift");
				String term = resultSet.getString("term");
				String title = resultSet.getString("title");
				String xtype = resultSet.getString("xtype");
				ZswsHRaShift zs = new ZswsHRaShift(eid, name, shift, term, title, xtype);
				lists.add(zs);
				datalength++;
			}
		} catch (SQLException e) {

			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		List<List> insertlist = new ArrayList<List>();
		//先删除今天之后的所有排班
		RecordSet rs0 = new RecordSet();
		String sql0="delete from uf_rypbxx where convert(date,term,20) >convert(date,getdate(),20)";
		rs0.execute(sql0);
		//让后插入今天之后的排班
		for (ZswsHRaShift zsws : lists) {
			List<String> insertparam = new ArrayList<String>();
			insertparam.add(zsws.getEid());
			insertparam.add(zsws.getName());
			insertparam.add(zsws.getShift());
			insertparam.add(zsws.getTerm());
			insertparam.add(zsws.getTitle());
			insertparam.add(zsws.getXtype());
			insertlist.add(insertparam);
			add++;
			}
		RecordSet rs1 = new RecordSet();
		String insertbatch = "insert into uf_rypbxx(eid,name,shift,term,title,xtype) values(?,?,?,?,?,?)";
		rs1.executeBatchSql(insertbatch, insertlist);

		jsonObject.put("code", returnCode);
		jsonObject.put("message", returnMessage);
		jsonObject.put("add", add);
		jsonObject.put("total", datalength);
		System.out.println(jsonObject.toString());
	}
	
	//同步排班（同步所有数据,覆盖同步）；
	public static void test5() {

		List<ZswsHRaShift> lists = new ArrayList<ZswsHRaShift>();
		JSONObject jsonObject = new JSONObject();
		String returnCode = "";
		String returnMessage = "";
		int add = 0;
		int update = 0;
		weaver.interfaces.datasource.DataSource ds = (weaver.interfaces.datasource.DataSource) StaticObj
				.getServiceByFullname(("datasource.HRDB"), weaver.interfaces.datasource.DataSource.class);
		java.sql.Connection conn = ds.getConnection();
		int datalength = 0;
		//查询视图所有数据
		try {

			String sql = "select eid,name,shift,convert(varchar(19),term,20) as term,title,xtype from zswsHRaShift order by eid ,term";
			PreparedStatement preparedStatement = conn.prepareStatement(sql);
			ResultSet resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {

				String eid = resultSet.getString("eid");
				String name = resultSet.getString("name");
				String shift = resultSet.getString("shift");
				String term = resultSet.getString("term");
				String title = resultSet.getString("title");
				String xtype = resultSet.getString("xtype");
				ZswsHRaShift zs = new ZswsHRaShift(eid, name, shift, term, title, xtype);
				lists.add(zs);
				datalength++;
			}
		} catch (SQLException e) {

			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		List<List> insertlist = new ArrayList<List>();
		//清空表数据
		RecordSet rs0 = new RecordSet();
		String sql0="truncate table uf_rypbxx";
		rs0.execute(sql0);
		
		for (ZswsHRaShift zsws : lists) {
			List<String> insertparam = new ArrayList<String>();
			insertparam.add(zsws.getEid());
			insertparam.add(zsws.getName());
			insertparam.add(zsws.getShift());
			insertparam.add(zsws.getTerm());
			insertparam.add(zsws.getTitle());
			insertparam.add(zsws.getXtype());
			insertlist.add(insertparam);
			add++;
			}
		RecordSet rs1 = new RecordSet();
		String insertbatch = "insert into uf_rypbxx(eid,name,shift,term,title,xtype) values(?,?,?,?,?,?)";
		rs1.executeBatchSql(insertbatch, insertlist);
		jsonObject.put("code", returnCode);
		jsonObject.put("message", returnMessage);
		jsonObject.put("add", add);
		jsonObject.put("update", update);
		jsonObject.put("total", datalength);
		System.out.println(jsonObject.toString());
	}
	
	//同步排班组数据
	public static void test6() {

		List<List> insertlist = new ArrayList<List>();
		JSONObject jsonObject = new JSONObject();
		weaver.interfaces.datasource.DataSource ds = (weaver.interfaces.datasource.DataSource) StaticObj
				.getServiceByFullname(("datasource.HRDB"), weaver.interfaces.datasource.DataSource.class);
		java.sql.Connection conn = ds.getConnection();
		int datalength = 0;
		try {

			String sql = "select * from zswsHRaShift_type";
			PreparedStatement preparedStatement = conn.prepareStatement(sql);
			ResultSet rs = preparedStatement.executeQuery();
			while (rs.next()) {
				List<String> insertparam=new ArrayList<String>();
				insertparam.add(rs.getString("card1"));
				insertparam.add(rs.getString("card2"));
				insertparam.add(rs.getString("card3"));
				insertparam.add(rs.getString("card4"));
				insertparam.add(rs.getString("CardTimes"));
				insertparam.add(rs.getString("endT1"));
				insertparam.add(rs.getString("endT2"));
				insertparam.add(rs.getString("endT3"));
				insertparam.add(rs.getString("endT4"));
				insertparam.add(rs.getString("IsDisabled"));
				insertparam.add(rs.getString("scan1"));
				insertparam.add(rs.getString("scan2"));
				insertparam.add(rs.getString("scan3"));
				insertparam.add(rs.getString("scan4"));
				insertparam.add(rs.getString("Shift"));
				insertparam.add(rs.getString("Title"));
				insertparam.add(rs.getString("xHours"));
				datalength++;
				insertlist.add(insertparam);
			}
		} catch (SQLException e) {

			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}


		RecordSet rs0 = new RecordSet();
		String sql0="delete from uf_pbzxx";
		rs0.execute(sql0);
		RecordSet rs1 = new RecordSet();
		String insertbatch = "insert into uf_pbzxx(card1,card2,card3,card4,CardTimes,endT1,endT2,endT3,endT4,IsDisabled,scan1,scan2,scan3,scan4,Shift,Title,xHours) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		rs1.executeBatchSql(insertbatch, insertlist);

		jsonObject.put("total", datalength);
		System.out.println(jsonObject.toString());
	}
	
	//同步排班组数据,覆盖同步
	public static void test7() {

		List<List> insertlist = new ArrayList<List>();
		JSONObject jsonObject = new JSONObject();
		weaver.interfaces.datasource.DataSource ds = (weaver.interfaces.datasource.DataSource) StaticObj
				.getServiceByFullname(("datasource.HRDB"), weaver.interfaces.datasource.DataSource.class);
		java.sql.Connection conn = ds.getConnection();
		int datalength = 0;
		try {

			String sql = "select * from zswsHRaShift_type";
			PreparedStatement preparedStatement = conn.prepareStatement(sql);
			ResultSet rs = preparedStatement.executeQuery();
			while (rs.next()) {
				List<String> insertparam=new ArrayList<String>();
				insertparam.add(rs.getString("card1"));
				insertparam.add(rs.getString("card2"));
				insertparam.add(rs.getString("card3"));
				insertparam.add(rs.getString("card4"));
				insertparam.add(rs.getString("CardTimes"));
				insertparam.add(rs.getString("endT1"));
				insertparam.add(rs.getString("endT2"));
				insertparam.add(rs.getString("endT3"));
				insertparam.add(rs.getString("endT4"));
				insertparam.add(rs.getString("IsDisabled"));
				insertparam.add(rs.getString("scan1"));
				insertparam.add(rs.getString("scan2"));
				insertparam.add(rs.getString("scan3"));
				insertparam.add(rs.getString("scan4"));
				insertparam.add(rs.getString("Shift"));
				insertparam.add(rs.getString("Title"));
				insertparam.add(rs.getString("xHours"));
				datalength++;
				insertlist.add(insertparam);
			}
		} catch (SQLException e) {

			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}


		RecordSet rs0 = new RecordSet();
		String sql0="truncate table uf_pbzxx";
		rs0.execute(sql0);
		RecordSet rs1 = new RecordSet();
		String insertbatch = "insert into uf_pbzxx(card1,card2,card3,card4,CardTimes,endT1,endT2,endT3,endT4,IsDisabled,scan1,scan2,scan3,scan4,Shift,Title,xHours) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		rs1.executeBatchSql(insertbatch, insertlist);

		jsonObject.put("total", datalength);
		System.out.println(jsonObject.toString());
	}
}
